package com.example.springboot.utils.jdbc;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * mysql操作数据库
 * 
 * @author admin
 * 
 */
public class JdbcUtil {

	private static final Log log = LogFactory.getLog(JdbcUtil.class);

	private static ThreadLocal<Connection> connect = new ThreadLocal<Connection>();

	// 数据库配置
	private static String URL = "jdbc:mysql://localhost:3306/test";
	private static String USER_NAME = "root";
	private static String PASSWORD = "123456";

	static {
		try {
			log.info("开始加载数据库连接配置文件........");
			/** 通过属性文件获取数据库连接的参数值 **/
			Properties properties = new Properties();
			InputStream stream = JdbcUtil.class.getClassLoader().getResourceAsStream("config.properties");
			properties.load(stream);
			/** 获取属性文件中的值 **/
			String driverClassName = properties.getProperty("jdbc.driverClassName");
			URL = properties.getProperty("jdbc.url");
			USER_NAME = properties.getProperty("jdbc.username");
			PASSWORD = properties.getProperty("jdbc.password");
			Class.forName(driverClassName);
			log.info("初始化数据库连接完成........");
		} catch (Exception e) {
			e.printStackTrace();
			log.error("加载数据库连接配置文件异常：" + e);
		}
	}

	/**
	 * 获取数据库连接
	 * 
	 * @return 数据库连接
	 */
	public static Connection getConnection() {
		Connection connection = connect.get();
		try {
			if (connection == null) {
				synchronized (JdbcUtil.class) {
					if (connection == null) {
						connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("获取数据库连接失败： " + e);
		}
		return connection;
	}

	/**
	 * 获取Statement
	 * 
	 * @return
	 */
	public static Statement getStatement() {

		Statement statement = null;
		try {
			statement = getConnection().createStatement();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("获取Statement异常： " + e);
		}
		return statement;
	}

	/**
	 * 开启事务
	 * 
	 */
	public static void begin() {
		try {
			Connection con = connect.get();// 获取当前线程的事务连接
			if (con == null)
				throw new SQLException("开启事务失败！");
			con.setAutoCommit(false);// 设置为手动提交
			connect.set(con);// 把当前事务连接放到connect中
		} catch (Exception e) {
			e.printStackTrace();
			log.error("开启事务失败： " + e);
		}
	}

	/**
	 * 提交事务
	 * 
	 */
	public static void commit() {
		try {
			Connection con = connect.get();// 获取当前线程的事务连接
			if (con == null) {
				throw new SQLException("没有事务不能提交！");
			}
			con.commit();// 提交事务
			con.close();// 关闭连接
			con = null;// 表示事务结束！
			connect.remove();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("提交事务失败： " + e);
		}
	}

	/**
	 * 回滚事务
	 * 
	 */
	public static void rollback() {
		try {
			Connection con = connect.get();// 获取当前线程的事务连接
			if (con == null) {
				throw new SQLException("没有事务不能回滚！");
			}
			con.rollback();
			con.close();
			con = null;
			connect.remove();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("回滚事务失败： " + e);
		}
	}

	/**
	 * 关闭连接
	 * 
	 */
	public static void close(Connection con) {
		try {
			if (con != null) {
				con.close();
				con = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("关闭数据库连接失败： " + e);
		}
	}

	/**
	 * 关闭连接
	 * 
	 */
	public static void close(Statement pstm) {
		try {
			if (pstm != null) {
				pstm.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("关闭数据库连接失败： " + e);
		}

	}

	/**
	 * 关闭连接
	 * 
	 */
	public static void close(PreparedStatement pstm) {
		try {
			if (pstm != null) {
				pstm.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("关闭数据库连接失败： " + e);
		}

	}

	/**
	 * 关闭连接
	 * 
	 */
	public static void close(Connection con, PreparedStatement pstm) {
		try {
			if (pstm != null) {
				pstm.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("关闭数据库连接失败： " + e);
		}
	}

	/**
	 * 关闭连接
	 * 
	 */
	public static void close(Connection con, Statement pstm) {
		try {
			if (pstm != null) {
				pstm.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("关闭数据库连接失败： " + e);
		}
	}

	/**
	 * 释放Connection
	 * 
	 */
	public static void release(Connection connection) {
		try {
			Connection con = connect.get();// 获取当前线程的事务连接
			if (connection != con) {// 如果参数连接，与当前事务连接不同，说明这个连接不是当前事务，可以关闭！
				if (connection != null && !connection.isClosed()) {// 如果参数连接没有关闭，关闭之！
					connection.close();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("释放Connection失败： " + e);
		}
	}

	/**
	 * 查询单条数据，并返回map结果集
	 * 
	 * @param sql
	 *            ：sql语句
	 * @return
	 */
	public static Map<String, Object> selectOne(String sql) {

		Long startTime = System.currentTimeMillis();
		log.info("查询单条数据sql: " + sql);
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			List<Map<String, Object>> list = select(sql);
			if (list != null && list.size() > 0) {
				map = list.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("查询数据库失败： " + e);
		}
		log.info("查询耗时: " + (System.currentTimeMillis() - startTime));
		return map;
	}

	/**
	 * 查询数据
	 * 
	 * @param sql
	 *            ：sql语句
	 * @return
	 */
	public static List<Map<String, Object>> select(String sql) {
		log.info("查询数据sql: " + sql);
		long startTime = System.currentTimeMillis();
		Connection connection = null;
		PreparedStatement pst = null;
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			connection = getConnection();
			pst = connection.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int index = 1; index < rsmd.getColumnCount(); index++) {
					String columnName = rsmd.getColumnName(index);
					Object columnValue = rs.getObject(index);
					map.put(columnName, columnValue);
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("查询数据库失败： " + e);
		} finally {
			close(connection, pst);
		}
		log.info("查询总耗时： " + (System.currentTimeMillis() - startTime));
		return list;
	}

	/**
	 * 查询数据
	 * 
	 * @param sql
	 *            ：sql语句
	 * @param clazz
	 *            :返回对象
	 * @return
	 */
	public static <T> T selectOne(String sql, Class<?> clazz) {
		log.info("查询数据sql: " + sql);
		List<T> list = select(sql, clazz);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * 查询数据
	 * 
	 * @param sql
	 *            ：sql语句
	 * @param clazz
	 *            :返回对象
	 * @return
	 */
	public static <T> List<T> select(String sql, Class<?> clazz) {
		log.info("查询数据sql: " + sql);
		Long startTime = System.currentTimeMillis();
		PreparedStatement pst = null;
		Connection connection = null;
		List<T> list = null;
		try {
			connection = getConnection();
			pst = connection.prepareStatement(sql);
			ResultSet resultSet = pst.executeQuery();
			list = populate(resultSet, clazz);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("查询数据库失败： " + e);
		} finally {
			close(connection, pst);
		}
		log.info("查询总耗时： " + (System.currentTimeMillis() - startTime));
		return list;
	}

	/**
	 * 将rs结果转换成对象列表
	 * 
	 * @param rs
	 *            :结果集
	 * @param clazz
	 *            ：实体类
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> populate(ResultSet rs, Class<?> clazz) throws Exception {

		List<T> list = new ArrayList<T>();
		ResultSetMetaData rsmd = rs.getMetaData();
		Field[] fields = clazz.getDeclaredFields();
		while (rs != null && rs.next()) {
			@SuppressWarnings("unchecked")
			T obj = (T) clazz.newInstance();// 构造业务对象实体
			for (int index = 1; index < rsmd.getColumnCount(); index++) {
				String columnName = rsmd.getColumnName(index);
				Object columnValue = rs.getObject(index);

				for (int j = 0; j < fields.length; j++) {
					Field field = fields[j];
					// 如果匹配进行赋值
					if (field.getName().equalsIgnoreCase(columnName)) {
						boolean flag = field.isAccessible();
						field.setAccessible(true);
						field.set(obj, getValue(field, columnValue));
						field.setAccessible(flag);
						break;
					}
				}
			}
			list.add(obj);
		}
		return list;
	}

	/**
	 * 将结果转为对应的字段类型
	 * 
	 * @param field
	 *            ：字段实体类
	 * @param value
	 *            ：字段对应的值
	 * @return
	 * @throws ParseException
	 */
	private static Object getValue(Field field, Object value) {

		try {

			if (value == null || "".equals(value) || "null".equals(value)) {
				return null;
			}
			String columnValue = value.toString().trim();
			Class<?> type = field.getType();
			if (type == byte.class || type == Byte.class) {
				return Integer.parseInt(columnValue);
			} else if (type == int.class || type == Integer.class) {
				String str = columnValue;
				if ("false".equals(str)) {
					return 0;
				} else if ("true".equals(str)) {
					return 1;
				} else {
					return Integer.parseInt(str);
				}
			} else if (type == float.class || type == Float.class) {
				return Float.parseFloat(columnValue);
			} else if (type == double.class || type == Double.class) {
				return Double.parseDouble(columnValue);
			} else if (type == Boolean.class) {
				String str = columnValue;
				boolean parseBoolean = Boolean.parseBoolean(str);
				return parseBoolean;
			} else if (type == String.class) {
				return columnValue;
			} else if (type == Date.class) {
				String str = columnValue;
				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date date = dateFormat.parse(str);
				return date;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 批量操作（新增、修改、删除）,改操作入库较慢
	 * 
	 * @param sqlList
	 *            :查询语句集合
	 * @return
	 */
	public static void batch(List<String> sqlList) {
		log.info("批量操作数据sql: " + sqlList);
		Long startTime = System.currentTimeMillis();
		Connection conn = null;
		Statement stmt = null;
		try {
			if (sqlList == null || sqlList.size() <= 0) {
				return;
			}
			conn = JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for (String sql : sqlList) {
				stmt.addBatch(sql);
			}
			// 获得执行后影响的行数。形成一个数组
			int[] result = stmt.executeBatch();
			log.error("批量插入数据结果： " + Arrays.toString(result));
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("批量插入数据异常： " + e);
		} finally {
			JdbcUtil.close(conn, stmt);
		}
		log.info("批量操作插入总耗时： " + (System.currentTimeMillis() - startTime));
	}

	/**
	 * 批量操作（新增、修改、删除）,sql使用？占位符代替
	 * 
	 * @param sql
	 *            :查询语句
	 * @param callback
	 *            :回调函数，用于处理批量参数设置
	 * @return
	 */
	public static void batch(String sql, SQLCallBack callback) {
		log.info("批量操作数据sql: " + sql);
		Long startTime = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			pstm = conn.prepareStatement(sql);
			callback.batch(pstm);
			pstm.executeBatch();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("批量插入数据异常： " + e);
		} finally {
			JdbcUtil.close(conn, pstm);
		}
		log.info("批量操作插入总耗时： " + (System.currentTimeMillis() - startTime));
	}

	/**
	 * 更新数据库（新增、修改、删除）
	 * 
	 * @param sql
	 *            :查询语句
	 * @return
	 */
	public static int update(String sql) {
		log.info("更新数据库（新增、修改、删除）数据sql: " + sql);
		Long startTime = System.currentTimeMillis();
		int result = 0;
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			pstm = conn.prepareStatement(sql);
			result = pstm.executeUpdate();
			if (result > 0) {
				log.info("更新数据库成功！ ");
			}
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("批量插入数据异常： " + e);
		} finally {
			JdbcUtil.close(conn, pstm);
		}
		log.info("更新数据库总耗时： " + (System.currentTimeMillis() - startTime));
		return result;
	}

	abstract static class SQLCallBack {
		// 批量更新、插入、删除
		public abstract void batch(PreparedStatement pstm) throws SQLException;
	}

	public static void main(String[] args) {

		String sql = "select * from student";
		// List<Student> list = JdbcUtil.select(sql, Student.class);
		// List<Map<String, Object>> list2 = JdbcUtil.select(sql);
		//
		// Map<String, Object> map = JdbcUtil.selectOne(sql);
		// Student st = JdbcUtil.selectOne(sql, Student.class);
		// System.out.println(list);

		sql = "INSERT INTO student(id,name,sex,remark,time) VALUES(?,CONCAT('姓名',?),?,?,?)";
		final Object[] params = new Object[] {};
		JdbcUtil.batch(sql, new SQLCallBack() {
			@Override
			public void batch(PreparedStatement pstm) throws SQLException {
				// 批量操作,设置 sql使用？占位符参数
				for (int i = 0; i <= params.length; i++) {
					pstm.setString(1, i + "");
					pstm.setString(2, i + "");
					pstm.setInt(3, 1);
					pstm.setString(4, "" + "188" + i + "88" + i + i + "66" + i);
					// pstm.setDate(5, new Date(System.currentTimeMillis()));
					pstm.addBatch();

					if ((i + 1) % 1000 == 0) {
						// 每执行一次
						// 就清一次sql缓存，否则每一次是执行1000，2000，3000...条sql语句而不是每一次执行1000条。
						pstm.executeBatch();
						pstm.clearBatch();
					}
				}
			}
		});

		// sql语句不再采用拼接方式，应用占位符问号的方式写sql语句。
		sql = "delete from student where id=?";
		int update = JdbcUtil.update(sql);
		System.out.println(update);
	}
}
